import sqlite3
import json

class Database:
    def __init__(self, db_path):
        self.conn = sqlite3.connect(db_path, check_same_thread=False)
        self.cursor = self.conn.cursor()

    def init_db(self):
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS jobs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                source_host TEXT NOT NULL,
                source_port TEXT NOT NULL,
                source_username TEXT NOT NULL,
                source_password TEXT NOT NULL,
                target_host TEXT NOT NULL,
                target_port TEXT NOT NULL,
                target_username TEXT NOT NULL,
                target_password TEXT NOT NULL,
                projects TEXT,  -- 新增字段用于存储项目数组字符串
                status INTEGER DEFAULT 0,
                progress INTEGER DEFAULT 0,
                error_message TEXT
            )
        ''')
        self.conn.commit()

    def add_job(self, job_data):
        projects = job_data.get('projects', "")  # 将项目数组转换为JSON字符串
        self.cursor.execute('''INSERT INTO jobs (name, source_host, source_port, source_username, source_password, 
                              target_host, target_port, target_username, target_password, projects)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            job_data['name'],
            job_data['source_host'],
            job_data['source_port'],
            job_data['source_username'],
            job_data['source_password'],
            job_data['target_host'],
            job_data['target_port'],
            job_data['target_username'],
            job_data['target_password'],
            projects
        ))
        self.conn.commit()
        return job_data

    def get_all_jobs(self):
        self.cursor.execute('SELECT id, name, source_host, source_port, source_username, source_password, target_host, target_port, target_username, target_password, projects, status, progress, error_message FROM jobs')
        jobs = self.cursor.fetchall()
        job_list = []
        for job in jobs:
            job_dict = {
                'id': job[0],
                'name': job[1],
                'source_host': job[2],
                'source_port': job[3],
                'source_username': job[4],
                'source_password': job[5],
                'target_host': job[6],
                'target_port': job[7],
                'target_username': job[8],
                'target_password': job[9],
                'projects': json.loads(job[10]) if job[10] else [],
                'status': job[11],
                'progress': job[12],
                'error_message': job[13]
            }
            job_list.append(job_dict)
        return job_list

    def get_job_by_id(self, job_id):
        self.cursor.execute('SELECT id, name, source_host, source_port, source_username, source_password, target_host, target_port, target_username, target_password, projects, status, progress, error_message FROM jobs WHERE id = ?', (job_id,))
        job = self.cursor.fetchone()
        if job:
            job_dict = {
                'id': job[0],
                'name': job[1],
                'source_host': job[2],
                'source_port': job[3],
                'source_username': job[4],
                'source_password': job[5],
                'target_host': job[6],
                'target_port': job[7],
                'target_username': job[8],
                'target_password': job[9],
                'projects': json.loads(job[10]) if job[10] else [],
                'status': job[11],
                'progress': job[12],
                'error_message': job[13]
            }
            return job_dict
        return None

    def get_jobs_by_status(self, status):
        self.cursor.execute('SELECT id, name, source_host, source_port, source_username, source_password, target_host, target_port, target_username, target_password, projects, status, progress, error_message FROM jobs WHERE status = ?', (status,))
        jobs = self.cursor.fetchall()
        job_list = []
        for job in jobs:
            job_dict = {
                'id': job[0],
                'name': job[1],
                'source_host': job[2],
                'source_port': job[3],
                'source_username': job[4],
                'source_password': job[5],
                'target_host': job[6],
                'target_port': job[7],
                'target_username': job[8],
                'target_password': job[9],
                'projects': json.loads(job[10]) if job[10] else [],
                'status': job[11],
                'progress': job[12],
                'error_message': job[13]
            }
            job_list.append(job_dict)
        return job_list

    def update_job_status(self, job_id, status, progress, error_message=None):
        if error_message:
            self.cursor.execute('''UPDATE jobs SET status = ?, progress = ?, error_message = ? WHERE id = ?''', (status, progress, error_message, job_id))
        else:
            self.cursor.execute('''UPDATE jobs SET status = ?, progress = ?, error_message = NULL WHERE id = ?''', (status, progress, job_id))
        self.conn.commit()

    def delete_job(self, job_id):
        self.cursor.execute('DELETE FROM jobs WHERE id = ?', (job_id,))
        self.conn.commit()